package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.model.CurriInfo;
import com.model.QuestionInfo;
import com.util.DBUtil;
import com.util.ModelData;
import com.util.ModelDatatwo;
import com.util.ModelQuestionInfo;

public class QuestionInfoDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;
	
	public int insertAll(QuestionInfo qi) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("insert into questionInfo values(null,?,?,?,?,?,?,?,?,?,0,1)");
			ps.setInt(1, qi.getCurId());
			ps.setString(2, qi.getTimuName());
			ps.setString(3, qi.getA());
			ps.setString(4, qi.getB());
			ps.setString(5, qi.getC());
			ps.setString(6, qi.getD());
			ps.setString(7, qi.getYes());
			ps.setString(8, qi.getJieXi());
			ps.setString(9, qi.getFenZhi());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int deleteById(int id) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update questionInfo set IsDelete=0 where TimuId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int updateBuId(QuestionInfo qi) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update questionInfo set CurId=?,TimuName=?,A=?,B=?,C=?,D=?,Yes=?,JieXi=?,FenZhi=? where TimuId=?");
			ps.setInt(1, qi.getCurId());
			ps.setString(2, qi.getTimuName());
			ps.setString(3, qi.getA());
			ps.setString(4, qi.getB());
			ps.setString(5, qi.getC());
			ps.setString(6, qi.getD());
			ps.setString(7, qi.getYes());
			ps.setString(8, qi.getJieXi());
			ps.setString(9, qi.getFenZhi());
			ps.setInt(10, qi.getTimuId());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public ModelQuestionInfo selectAll(int page,int rows,String name,int km) {
		ModelQuestionInfo m=new ModelQuestionInfo();
		ArrayList<QuestionInfo> list=new ArrayList<QuestionInfo>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId limit ?,?";
		if(name!=null&&!"".equals(name)&&km==0) {
			sql="select questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%' limit ?,?";
		}else if(km!=0){
			sql="select questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%' and questionInfo.CurId="+km+" limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				QuestionInfo qi=new QuestionInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11),rs.getString(13));
				list.add(qi);
			}
			m.setRows(list);
			m.setTotal(selectCount(name,km));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return m;
	}
	
	public int selectCount(String name,int km) {
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from questionInfo,curriInfo where questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId";
		if(name!=null&&!"".equals(name)&&km==0) {
			sql="select count(*) from questionInfo,curriInfo where questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%'";
		}else if(km!=0){
			sql="select count(*) from questionInfo,curriInfo where questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%' and questionInfo.CurId="+km;
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	
	public int selectByQbBjCount() {
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from (select count(*) from questionInfo where questionInfo.isdelete=1 group by CurId)stu";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public ArrayList<CurriInfo> selectByClassName() {
		ArrayList<CurriInfo> list=new ArrayList<CurriInfo>();
		conn=DBUtil.getConn();
		String sql="select curriInfo.CurName from questionInfo,curriInfo where questionInfo.isdelete=1 and questionInfo.CurId=curriInfo.CurId group by questionInfo.CurId order by CurriInfo.CUrName";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				CurriInfo c=new CurriInfo();
				c.setCurName(rs.getString(1));
				list.add(c);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public ArrayList<QuestionInfo> selectByQbXueSheng() {
		ArrayList<QuestionInfo> list=new ArrayList<QuestionInfo>();
		conn=DBUtil.getConn();
		String sql="select questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.isdelete=1 and curriInfo.CurId=questionInfo.CurId order by curriInfo.CurName";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				QuestionInfo qi=new QuestionInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11),rs.getString(13));
				list.add(qi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	
	public ArrayList<QuestionInfo> selectAll(String name) {
		ArrayList<QuestionInfo> list=new ArrayList<QuestionInfo>();
		conn=DBUtil.getConn();
		String sql="select questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.IsDelete=1 and questionInfo.CurId=curriInfo.CurId and curriInfo.CurName=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			rs=ps.executeQuery();
			while(rs.next()) {
				QuestionInfo qi=new QuestionInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11),rs.getString(13));
				list.add(qi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public ModelQuestionInfo selectAllBystate(int page,int rows,String name,int km,int sjid,int sjcount) {
		ModelQuestionInfo m=new ModelQuestionInfo();
		ArrayList<QuestionInfo> list=new ArrayList<QuestionInfo>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select DISTINCT questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where state=0 and questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId limit ?,?";
		if(km!=0&&sjid==0) {
			sql="select DISTINCT questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.curid="+km+" and state=0 and questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%' limit ?,?";
		}else if(km!=0&&sjid!=0){
			sql="select DISTINCT questionInfo.*,curriInfo.CurName from questionInfo inner join paperTitleInfo,curriInfo,paperInfo p where questionInfo.curid="+km+" and p.paperId="+sjid+" and questionInfo.TimuId<>PaperTitleInfo.Timuid and questionInfo.state=0"
					+ " and questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%' limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				QuestionInfo qi=new QuestionInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11),rs.getString(13));
				list.add(qi);
			}
			//关闭连接
			DBUtil.close(rs, ps, conn);
			m.setRows(list);
			m.setTotal(selectAllBystatecount(name,km,sjid,sjcount));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return m;
	}
	
	
	
	public int selectAllBystatecount(String name,int... km) {
		int count=-1;
		if(km.length==3) {
			if(km[2]!=0) {
				count=0;
			}
		}
		conn=DBUtil.getConn();
		String sql="select count(*) from (select DISTINCT questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where state=0 and questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId) p";
		if(km[0]!=0&&km[1]==0&&count==0) {
			sql="select count(*) from (select DISTINCT questionInfo.*,curriInfo.CurName from questionInfo,curriInfo where questionInfo.curid="+km[0]+" and state=0 and questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%') p";
		}else if(km[0]!=0&&km[1]!=0){
			sql="select count(*) from (select DISTINCT questionInfo.*,curriInfo.CurName from questionInfo inner join paperTitleInfo,curriInfo,paperInfo p where questionInfo.curid="+km[0]+" and p.paperId="+km[1]+" and questionInfo.TimuId<>PaperTitleInfo.Timuid and questionInfo.state=0"
					+ " and questionInfo.isDelete=1 and questionInfo.CurId=curriInfo.CurId and questionInfo.TimuName like'%"+name+"%') p";
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public int updatestateByid(int id) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update questionInfo set state=1 where TimuId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int updateAllstate() {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update questionInfo set state=0 where state=1");
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	
		public ArrayList<QuestionInfo> selectShiJue(int paperid) {
		ArrayList<QuestionInfo> list=new ArrayList<QuestionInfo>();
		conn=DBUtil.getConn();
		String sql="select distinct q.*,pu.starttime,pu.endtime,pi.paperName from questionInfo q,paperTitleInfo p,publishExamPlan pu,paperInfo pi where p.timuid=q.timuid and p.paperid=? and pu.paperid=p.paperid and p.paperid=pi.paperId";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, paperid);
			rs=ps.executeQuery();
			String start="";
			String end="";
			while(rs.next()) {
				QuestionInfo qi=new QuestionInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getInt(11),"");
				start=rs.getString(13);
				end=rs.getString(14);
				qi.setPaperName(rs.getString(15));
				qi.setPaperId(paperid);
				list.add(qi);
			}
//			
//			Date star = new Date(start);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String dateStr = start;
			String dateend = end;
			Date dates = null;
			Date ends = null;
			Date date=new Date();
			try {
				dates = sdf.parse(dateStr);
				ends=sdf.parse(dateend);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			long jg=ends.getTime()-date.getTime();
			double days = jg / 1000 / 60 / 60 / 24;
			
			double daysRound = Math.floor(days);
			
			double hours = jg/ 1000 / 60 / 60 - (24 * daysRound);
			
			double hoursRound = Math.floor(hours);
			
			double minutes = jg / 1000 /60 - (24 * 60 * daysRound) - (60 * hoursRound);
			
			double minutesRound = Math.floor(minutes);
			
			double seconds = jg/ 1000 - (24 * 60 * 60 * daysRound) - (60 * 60 * hoursRound) - (60 * minutesRound);
			
			double secondsRound = Math.floor(seconds);
			int hoursRounds=(int)hoursRound;
			int minutesRounds=(int)minutesRound;
			int secondsRounds=(int)secondsRound;
			
			var time = hoursRounds +":"+minutesRounds+":"+secondsRounds;
			
			
			list.get(0).setJgsj(time);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
			
	public ModelData selectAll(int sjid) {
		ModelData md=null;
		String title="";
		ArrayList<QuestionInfo> list=new ArrayList<QuestionInfo>();
		conn=DBUtil.getConn();
		String sql="select q.*,pi.paperName from questionInfo q,paperTitleInfo pt,paperInfo pi where q.timuid=pt.timuid and pt.paperId=? and pi.paperid=pt.paperid";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, sjid);
			rs=ps.executeQuery();
			while(rs.next()) {
				QuestionInfo qi=new QuestionInfo();
				qi.setType(1);
				qi.setQuestionStem(rs.getString(3));
				String[] str=new String[4];
				str[0]=rs.getString(4);
				str[1]=rs.getString(5);
				str[2]=rs.getString(6);
				str[3]=rs.getString(7);
				qi.setOptions(str);
				str=new String[1];
				str[0]=rs.getString(8);
				qi.setAnswer(str);
				qi.setAnalysis(rs.getString(9));
				
				qi.setTimuId(rs.getInt(1));
				qi.setPaperId(sjid);
				title=rs.getString(13);
				list.add(qi);
			}
			md=new ModelData();
			md.setTitle(title);
			ModelDatatwo[] mdt=new ModelDatatwo[1];
			mdt[0]=new ModelDatatwo();
			mdt[0].setTitle("单项选择题");
			
			mdt[0].setValues(list);
			md.setExam(mdt);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return md;
	}
	
	public int selectcountById(int sjid) {
		int row=0;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select count(*) from questionInfo q,paperTitleInfo pt,paperInfo pi where q.timuid=pt.timuid and pt.paperId=? and pi.paperid=pt.paperid");
			ps.setInt(1, sjid);
			rs=ps.executeQuery();
			while(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
}
